<?xml version="1.0" encoding="UTF-8" ?>
<objects default-lazy-init="false">
  <import resource="barcode2.xml"/>
  <import resource="plugintree.xml"/>
  <import resource="mabu.xml"/>
  <import resource="inventory.xml"/>
  <import resource="plugin.xml"/>
  <object name="messageSource" type="SBF.MessageSource">
      <property name="ResourceName">message.txt</property>
  </object>
  <object name="queryBuilder" type="sbfui.QueryBuilder" singleton="false" lazy-init="true">
    <property name="dataSource">
      <ref object="dataSource"/>
    </property>
    <property name="ConditionForm"><ref object="condition"/></property>
  </object>
  <object name="loginService" type="sbf.LoginService">
    <property name="dataSource">
      <ref object="dataSource"/>
    </property>
    <property name="user">
      <ref object="user"/>
    </property>
  </object>
  <object name="user" type="sbf.User"/>
  <object name="adoTemplate" type="sbf.AdoTemplate">

    <property name="DataSource">
      <ref object="dataSource"/>
    </property>
    <property name="QueryMap">
      <dictionary>
        <entry key="coilGrade">  SHAPE {select ordername,coils.spoilage,barcode,orderno,coils.id,coilunit,coilno,coils.colorname,sigangno,machineid , width,maxerror,maxerror1,maxerror2,length,gradestandard,floatingrate,shade from coils inner join color on coils.colorid=color.colorid  inner join gongyi on coils.orderid=gongyi.orderid inner join orders on
                            coils.orderid=orders.orderid
                            where coils.id={1}}  AS Coils APPEND ({SELECT coilid,wrongid,value,rank as koufen FROM wrongs INNER JOIN coils on wrongs.coilid=coils.id  where coils.id={1}}  AS wrongs RELATE 'id' TO 'coilid') AS wrongs ,sum(wrongs.koufen) as rank
        </entry>
     


        <entry key="getcustomer">select * from customerinfo where id=#id# and name like #name#</entry>
        <entry key="getcustomer1">select * from customerinfo where id=#id#</entry>
        <entry key="getcustomer2">select * from customerinfo where {id=#id#} and {name like #name#}</entry>
      </dictionary>
    </property>
  </object>
  <object name="transaction" type="sbf.AdoTransaction" singleton="false">
    <property name="DataSource">
      <ref object="dataSource"/>
    </property>
  </object>
  <object name="transactionManager" type="mabu.TransactionManager">
  </object>
  <object name="keyGenerate" type="mabu.KeyGenerate">
    <property name="AdoTemplate">
      <ref object="adoTemplate"/>
    </property>
  </object>
  <object name="baseDataService" type="mabu.BaseDataService">
    <property name="AdoTemplate">
      <ref object="adoTemplate"/>
    </property>
  </object>
  <object name="orderService" type="mabu.OrderService">
    <property name="barcode">
      <ref object="barcode"/>
    </property>
    <property name="QueryMaps">
      <dictionary>
        <entry key="GetCoilsByDate">
                  SELECT *
FROM coils_v
WHERE (coildate>='{1}')
        </entry>

        <entry key="GetCoilsByOrderId">
          <value>
                      select * from coils_v where orderid={1}
          </value>
        </entry>
        <entry key="orderStatus">
          <value><![CDATA[SELECT dbo.Orders.OrderId, ordername, dbo.Orders.OrderNo, startdate, enddate, 
    isnull(SUM(dbo.Coils.Length),0) AS produceQuantity, isnull(SUM(dbo.Coils.Length),0) * 1.094 as produceQuantityYds,COUNT(dbo.Coils.ID) AS coilcount, 
    dbo.Orders.quantity,quantity*1.094 as quantityyds, (CASE WHEN quantity < =SUM(dbo.Coils.Length) 
    THEN '完成' ELSE '未完成' END) AS status, SUM(CASE WHEN isnull(packageno, 0) 
    <> 0 THEN 1 ELSE 0 END) AS packagecount
FROM dbo.Orders LEFT OUTER JOIN
    dbo.Coils ON dbo.Orders.OrderId = dbo.Coils.OrderID
{1}
GROUP BY dbo.Orders.OrderId, dbo.Orders.OrderNo, dbo.Orders.quantity, ordername, 
    startdate, enddate ]]></value>
        </entry>
      </dictionary>
    </property>
    <property name="AdoTemplate">
      <ref object="adoTemplate"/>
    </property>
  </object>
  <object name="labelService" type="Label.LabelService" singleton="false">
    <property name="AdoTemplate">
      <ref object="adoTemplate"/>
    </property>
  </object>

  <object name="labelFormFactory" type="Label.FormFactory"/>
  <object name="labelPageDefineForm" type="SBFUI.LocalizeFormFactoryObject" lazy-init="true">
    <property name="Form">
      <object name="labelPageDefineForm" factory-object="labelFormFactory" factory-method="CreateInstance">
        <constructor-arg>labelPageDefine</constructor-arg>
        <property name="labelService">
          <ref object="labelService"/>
        </property>
      </object>
    </property>
  </object>
<!-- 等级标准 4分制 -->
  <object name="four" type="mabu.FourGradeNo36" singleton="false"/>
  <!-- 等级标准 10分制 -->
  <object name="ten" type="mabu.TenGrade" singleton="false"/>
  <object name="reportService" type="Mabu.ReportService">
    <property name="barcode">
      <ref object="barcode"/>
    </property>
    <property name="AdoTemplate">
      <ref object="adoTemplate"/>
    </property>
    <property name="queryMap">
      <dictionary>
             <entry key="order_v">
            <![CDATA[
            SELECT     dbo.HarborInfo.name AS harborname, dbo.factoryInfo.name AS factoryname, dbo.Orders.OrderNo, dbo.Orders.OrderName, dbo.gongyi.kezhong, 
                      dbo.gongyi.menfu, LengthDW_2.name AS menfudw, dbo.gongyi.zhuji, dbo.gongyi.others, dbo.gongyi.[content], dbo.gongyi.inspector, 
                      dbo.gongyi.tanxing, dbo.gongyi.huanbao, dbo.gongyi.selaodu, dbo.gongyi.shougan, dbo.gongyi.machi, dbo.gongyi.weimi, dbo.package.biaoqian, 
                      dbo.package.yaofeng, dbo.package.jiaodai, dbo.package.zhiguan, dbo.package.tangjin, dbo.package.meibaoshuliang, dbo.package.meijuanmishu, 
                      dbo.package.pingpi, dbo.package.hunbao, dbo.package.matou, dbo.package.chuanyang, dbo.package.needpackage, dbo.Orders.startdate, 
                      dbo.Orders.enddate, dbo.Orders.quantity, dbo.Orders.OrderId, dbo.CustomerInfo.name AS customername, dbo.gongyi.kezhongdw, 
                      dbo.LengthDW.name AS lengthdw, dbo.gongyi.menfu_original, dbo.Orders.quantity_original, dbo.Orders.gradestandard, dbo.Orders.floatingrate, 
                      dbo.Orders.employeeid, dbo.Orders.remark2, dbo.Orders.remark1,dbo.Orders.flowno
FROM         dbo.package INNER JOIN
                      dbo.Orders INNER JOIN
                      dbo.LengthDW ON dbo.Orders.lengthDwId = dbo.LengthDW.id ON dbo.package.orderid = dbo.Orders.OrderId INNER JOIN
                      dbo.CustomerInfo ON dbo.Orders.customerId = dbo.CustomerInfo.id LEFT OUTER JOIN
                      dbo.gongyi LEFT OUTER JOIN
                      dbo.LengthDW AS LengthDW_2 ON dbo.gongyi.menfudw = LengthDW_2.id ON dbo.Orders.OrderId = dbo.gongyi.OrderId LEFT OUTER JOIN
                      dbo.factoryInfo ON dbo.Orders.factoryId = dbo.factoryInfo.id LEFT OUTER JOIN
                      dbo.HarborInfo ON dbo.Orders.HarborId = dbo.HarborInfo.id
            ]]>
        </entry>
        <entry key="coilcheckreport">
                      SHAPE {select flowno,ordername,coildate,qualltyNO,coils.colorno,barcode,orderno,coils.id,coils.spoilage,coilunit,coilno,coils.colorname,coils.designno,sigangno,machineid ,width_original as width,lengthdw.name as widthunit,maxerror,maxerror1,maxerror2,length,gradestandard,floatingrate,shade from coils inner join gongyi on coils.orderid=gongyi.orderid inner join orders on
                              coils.orderid=orders.orderid  inner join lengthdw on gongyi.menfudw=lengthdw.id  inner join color on coils.colorid=color.colorid
                              where coils.id={1}}  AS Coils APPEND ({SELECT coilid,wrongid,value,rank as koufen FROM wrongs INNER JOIN coils on wrongs.coilid=coils.id  where coils.id={1}}  AS wrongs RELATE 'id' TO 'coilid') AS wrongs ,sum(wrongs.koufen) as rank




        </entry>
        <entry key="ordercheckreport">
          <value>
                           SHAPE {select coils.colorno,coils.colorname,coils.designno,coils.id,coilno,coilunit,lengthdw.name as widthunit,coils.spoilage,sigangno,sigangno as gangno,machineid , width,width_original as menfu_original,length,maxerror,maxerror1,maxerror2,rks,shade,gradestandard,floatingrate from coils inner join gongyi on coils.orderid=gongyi.orderid
                           inner join orders on coils.orderid=orders.orderid inner join lengthdw on gongyi.menfudw=lengthdw.id inner join color on coils.colorid=color.colorid 
                              where coils.orderid={1} {2} {3} {4}  {5}}  AS Coils APPEND ({SELECT coilid,wrongid,value,rank as koufen FROM wrongs INNER JOIN coils on wrongs.coilid=coils.id  where coils.orderid={1} {2} {3} {4}}  AS wrongs RELATE 'id' TO 'coilid') AS wrongs,count(wrongs.wrongid) as wrongcount,sum(wrongs.koufen) as rank
          </value>
        </entry>
        <entry key="ordersubstandardproduct">
          <value>

  SELECT  orderno, Orders.OrderName,
        coilcolor_quantity_v.length, color.colorno, color.quantity, color.buy,
        color.designno ,  coilcolor_quantity_v.length*1.094 as lengthyds,SubstandardProduct,gradeb,gradeb*1.094 as gradebyds,SubstandardProduct * 1.094 as SubstandardProductyds,coilcolor_quantity_v.spoilage,coilcolor_quantity_v.spoilage*1.094 as spoilageyds
  FROM Orders INNER JOIN
        CustomerInfo ON Orders.customerId = CustomerInfo.id INNER JOIN
        color ON Orders.OrderId = color.orderid INNER JOIN
            (SELECT colorid, orderid, SUM(length) AS length, SUM(SubstandardProduct)
        AS SubstandardProduct,sum(spoilage) as spoilage,sum(gradeb) as gradeb
  FROM orders_quality_v {1}
  GROUP BY colorid, orderid) coilcolor_quantity_v ON
        color.colorid = coilcolor_quantity_v.colorid AND
        color.orderid = coilcolor_quantity_v.OrderID
       {2}

          </value>
        </entry>
        <entry key="factoryprocessingamount">
          <value>
            <value>select factoryname,sum(length) as quantity from factoryprocessingamount_v {1} group by factoryid,factoryname</value>
          </value>
        </entry>
        <entry key="customerquantity">
          <value>SELECT  CustomerInfo.name AS customername, Orders.OrderName,
        coilcolor_quantity_v.length, color.colorno, color.quantity,color.quantity*1.094 as quantityyds, color.buy,
        color.designno ,  coilcolor_quantity_v.length*1.094 as lengthyds
  FROM Orders INNER JOIN
        CustomerInfo ON Orders.customerId = CustomerInfo.id INNER JOIN
        color ON Orders.OrderId = color.orderid INNER JOIN
            (SELECT OrderID, colorid, SUM(Length) AS length
           FROM dbo.Coils  {1}
           GROUP BY colorid, OrderID) coilcolor_quantity_v ON
        color.colorid = coilcolor_quantity_v.colorid AND
        color.orderid = coilcolor_quantity_v.OrderID
       {2}</value>
        </entry>
        <entry key="factorysubstandardproduct">
          <value>SELECT  factoryinfo.name as factoryname, Orders.OrderName,
        coilcolor_quantity_v.length, color.colorno, color.quantity, color.buy,
        color.designno ,  coilcolor_quantity_v.length*1.094 as lengthyds,SubstandardProduct,SubstandardProduct * 1.094 as SubstandardProductyds,gradeb ,gradeb *1.094 as gradebyds
  FROM Orders INNER JOIN
        CustomerInfo ON Orders.customerId = CustomerInfo.id INNER JOIN
        color ON Orders.OrderId = color.orderid INNER JOIN
            (SELECT colorid, orderid, SUM(length) AS length, SUM(SubstandardProduct)
        AS SubstandardProduct ,sum(gradeb) as gradeb
  FROM orders_quality_v {1}
  GROUP BY colorid, orderid) coilcolor_quantity_v ON
        color.colorid = coilcolor_quantity_v.colorid AND
        color.orderid = coilcolor_quantity_v.OrderID inner join factoryinfo on orders.factoryid=factoryinfo.id
       {2}

          </value>
        </entry>
        <entry key="qualityreport">
          <value><![CDATA[SELECT designno,ordername,colorname, customername, SUM(normalproduct) AS normalproduct, SUM(gradeb) 
        AS gradeb, SUM(SubstandardProduct) AS substandardproduct, SUM(normalproduct)+SUM(gradeb)+SUM(SubstandardProduct) as total,
        (case when  (SUM(normalproduct)+SUM(gradeb)+SUM(SubstandardProduct))<>0 then
        SUM(normalproduct)/(SUM(normalproduct)+SUM(gradeb)+SUM(SubstandardProduct)) else 0 end) as rate,sum(spoilage) as spoilage
  FROM qualityReport_v   where {1}  GROUP BY customerid, customername, colorno, colorname,ordername,designno]]></value>
        </entry>
        <entry key="employeeworkreport">
          <value><![CDATA[ SELECT ordername, employeename, designno,colorno,SUM(coilcount) AS coilcount, SUM(packagecount) 
        AS packagecount,sum(length) as length ,sum(length)*1.094 as lengthyds 
  FROM (SELECT ordername,dbo.Coils.CoilStuffID AS employeeid, 
                dbo.EmployeeInfo.name AS employeename,designno,colorno, COUNT(*) AS coilcount, 
                0 AS packagecount,sum(length) as length
          FROM dbo.Coils LEFT OUTER JOIN
                dbo.EmployeeInfo ON dbo.Coils.CoilStuffID = dbo.EmployeeInfo.id
                inner join orders on coils.orderid=orders.orderid
      where {1}
          GROUP BY dbo.Coils.CoilStuffID, dbo.EmployeeInfo.name,designno,colorno,ordername
          UNION ALL                                  
          SELECT ordername,dbo.Coils.packageStuffID AS employeeid, 
                dbo.EmployeeInfo.name AS employeename,designno,colorno, 0 AS coilcount, COUNT(*) 
                AS packagecount,0
          FROM dbo.Coils LEFT OUTER JOIN
                dbo.EmployeeInfo ON 
                dbo.Coils.packageStuffID = dbo.EmployeeInfo.id   inner join orders on coils.orderid=orders.orderid
          WHERE packageno <> 0 and {2}
          GROUP BY dbo.Coils.packageStuffID, dbo.EmployeeInfo.name,designno,colorno,ordername) v
  GROUP BY employeeid, employeename,designno,colorno ,ordername
  ]]></value>
        </entry>
      </dictionary>
    </property>
  </object>

</objects>
